﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DBUtility;

namespace SQLServerDAL
{
    /// <summary>
    /// 附件管理
    /// </summary>
    public class AttachmentsBase
    {
        private SqlConnection Conn;

        public AttachmentsBase()
        {
            Conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction);
        }
 
        /// <summary>
        /// 匹配 complaintId 获取附件序列
        /// </summary>
        /// <param name="complaintId"></param>
        /// <returns></returns>
        public Model.AttachmentsBase[] GetAttachments(string regardingObjectId)
        {
            string SQL = "SELECT * FROM AttachmentsBase WHERE RegardingObjectId = @RegardingObjectId ORDER BY LineNum ASC";
            SqlParameter[] Params = new SqlParameter[] { new SqlParameter("@RegardingObjectId", SqlDbType.UniqueIdentifier) };
            Params[0].Value = new Guid(regardingObjectId);

            Model.AttachmentsBase[] _attachments = null;
            Model.AttachmentsBase _item = null;
            try
            {
                DataTable dt = SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL, Params);
                if (dt != null)
                {
                    _attachments = new Model.AttachmentsBase[dt.Rows.Count];

                    int i = 0;
                    foreach (DataRow dr in dt.Select(""))
                    {
                        _item = new Model.AttachmentsBase(dr["AttachmentId"].ToString(), dr["FileName"].ToString(), dr["FilePath"].ToString(), dr["ExtName"].ToString(), int.Parse(dr["FileType"].ToString()), decimal.Parse(dr["FileSize"].ToString()), dr["Notes"].ToString(), dr["LineNum"].ToString());
                        _attachments[i] = _item;
                        i++;
                    }

                    dt.Clear();
                    dt.Dispose();
                }
            }
            catch
            {
                return null;
            }

            return _attachments;
        }

        #region 添加 / 修改附件
        const string SQL_INSERT_Attachments = "INSERT INTO AttachmentsBase(RegardingObjectId,FileName,FilePath,ExtName,FileSize,FileType,RegardingObjectIdName,RegardingObjectTypeCode,Notes,LineNum) VALUES(";
        const string SQL_DELETE_Attachments = "DELETE FROM AttachmentsBase WHERE RegardingObjectId = @RegardingObjectId; SELECT @ERR=@@ERROR;";

        /// <summary>
        /// 添加 / 修改附件
        /// </summary>
        /// <param name="_complaint"></param>
        /// <returns></returns>
        public string AttachmentsAdmin(Model.AttachmentsBase[] items, string regardingObjectId)
        {
            StringBuilder SQL = new StringBuilder();
            SQL.Append("Declare @ERR INT;");

            SqlParameter objParm = new SqlParameter("@RegardingObjectId", SqlDbType.UniqueIdentifier, 16);
            objParm.Value = new Guid(regardingObjectId);

            SqlCommand cmd = new SqlCommand();

            cmd.Parameters.Add(objParm); 

            string result = "";
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
            {
                conn.Open();
                ///设置隔离级别,启动数据库事务
                SqlTransaction trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);

                SQL.Append(SQL_DELETE_Attachments);

                try
                {
                    SqlParameter[] attachmentsParms = null;

                    int i = 0;
                    if (items != null && items.Length > 0)
                    {
                        foreach (Model.AttachmentsBase _items in items)
                        {
                            attachmentsParms = GetAttachmentsParameters(i);
                            attachmentsParms[0].Value = _items.FileName;
                            attachmentsParms[1].Value = _items.FilePath;
                            attachmentsParms[2].Value = _items.ExtName;
                            attachmentsParms[3].Value = _items.FileSize;
                            attachmentsParms[4].Value = _items.FileType; ; 
                            attachmentsParms[5].Value = _items.RegardingObjectIdName;
                            attachmentsParms[6].Value = _items.RegardingObjectTypeCode;
                            attachmentsParms[7].Value = _items.Notes;
                            attachmentsParms[8].Value = _items.LineNum;

                            SQL.Append(SQL_INSERT_Attachments).Append("@RegardingObjectId").Append(", @FileName").Append(i).Append(", @FilePath").Append(i).Append(", @ExtName").Append(i).Append(", @FileSize").Append(i).Append(", @FileType").Append(i).Append(", @RegardingObjectIdName").Append(i).Append(", @RegardingObjectTypeCode").Append(i).Append(", @Notes").Append(i).Append(", @LineNum").Append(i).Append("); SELECT @ERR=@@ERROR;");

                            //遍历绑定参数
                            foreach (SqlParameter parm in attachmentsParms)
                                cmd.Parameters.Add(parm);

                            i++;
                        }
                    }

                    if (conn.State.ToString() != "Open")
                        conn.Open();

                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = trans.Connection;
                    cmd.Transaction = trans;

                    cmd.CommandText = SQL.Append("SELECT @ERR").ToString();

                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        rdr.Read();
                        // 如果错误的数量不为零, 抛出异常终止操作
                        if (rdr.GetInt32(0) != 0)
                        {
                            trans.Rollback();
                        }
                        else
                        {
                            result = "succeeded" + "|" + regardingObjectId;
                        }
                    }
                    ///提交事务
                    trans.Commit();
                    cmd.Parameters.Clear();
                }
                catch (Exception e)
                {
                    result = e.Message;
                    ///事务回滚
                    trans.Rollback();
                }
                finally
                {
                    SQL = null;
                    conn.Close();
                    conn.Dispose();
                }
            }

            return result;
        } 
        /// <summary>
        /// 初始化参数序列, 附件库
        /// </summary>
        /// <returns></returns>
        private static SqlParameter[] GetAttachmentsParameters(int i)
        {
            SqlParameter[] parms = SqlHelper.GetCachedParameters(SQL_INSERT_Attachments + i);

            if (parms == null)
            {
                parms = new SqlParameter[] {
					new SqlParameter("@FileName" + i, SqlDbType.VarChar, 256),
                    new SqlParameter("@FilePath" + i, SqlDbType.VarChar, 256),
                    new SqlParameter("@ExtName" + i, SqlDbType.VarChar, 64),
                    new SqlParameter("@FileSize" + i, SqlDbType.Decimal, 18),
                    new SqlParameter("@FileType" + i, SqlDbType.Int),
					new SqlParameter("@RegardingObjectIdName" + i, SqlDbType.NVarChar, 400),
					new SqlParameter("@RegardingObjectTypeCode" + i, SqlDbType.Int, 4),
                    new SqlParameter("@Notes" + i, SqlDbType.NVarChar, 1000),
                    new SqlParameter("@LineNum" + i, SqlDbType.VarChar, 64),
                };

                SqlHelper.CacheParameters(SQL_INSERT_Attachments + i, parms);
            }

            return parms;
        }
        #endregion


        #region 删除
        /// <summary>
        /// 匹配外键删除文件记录
        /// </summary>
        /// <param name="regardingObjectId"></param>
        /// <returns></returns>
        public string DeleteAttachmentsByRegardingObjectId(string regardingObjectId)
        {  
            SqlParameter[] parameters = new SqlParameter[] { 
                new SqlParameter("@RegardingObjectId", SqlDbType.UniqueIdentifier), 
            };
            parameters[0].Value = new Guid(regardingObjectId);

            string result = "";
            try
            {
                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, "DELETE FROM AttachmentsBase WHERE RegardingObjectId = @RegardingObjectId", parameters);
                result = "succeeded";
            }
            catch(Exception err)
            {
                result = err.Message;
            }

            return result; 
        }
        #endregion
    }
}
